DROP TABLE IF EXISTS jms_ods.oms_waybill_extend ;
CREATE EXTERNAL TABLE jms_ods.oms_waybill_extend (
id                       bigint        comment  'id' ,
waybill_no               string        comment  '运单号' ,
package_name             timestamp     comment  '集包地名称' ,
package_code             string        comment  '集包地code' ,
input_time               timestamp     comment  '录入时间' ,
collection_package_type  tinyint       comment  '集包类型, 1:网点集包, 2:中心集包, 3:集散点集包' ,
is_privacy               tinyint       comment  '是否私密面单:1是 0否'
)
    COMMENT  '运单表扩展表'
    PARTITIONED BY (dt STRING COMMENT '时间分区')
    STORED AS PARQUET
    LOCATION '/dw/hive/jms_ods.db/external/oms_waybill_extend'
    TBLPROPERTIES (
        'parquet.column.index.access' = 'true'
        );

-- add columns by kumiler on 2023-08-11
alter table jms_ods.oms_waybill_extend
add columns (
transport_type_code string comment '运输类型编号',
destination_source_code int comment '目的地来源编号',
pick_parent_network_code string comment '一级（所属）网点编号',
pick_parent_network_name string comment '一级（所属）网点名称',
settle_customer_code string comment '结算客户编号',
settle_customer_name string comment '结算客户名称',
receiver_privacy_phone string comment '收件人隐私手机号',
self_vip int comment '自有客户 5-vip 6-菜鸟vip',
customer_label string comment '客户打标',
on_demand int comment '按需配送打标',
marketing_activity_type_name string comment '市场活动类型名称',
marketing_activity_type_code string comment '市场活动类型code',
fourth_address_id bigint comment '四级地址id',
fourth_address_name string comment '四级地址'
    ) cascade ;